import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

interface PBSLevel {
  id?: number;
  level1_area: string;
  level2_analysis: string;
  level3_subitem: string;
  full_path: string;
  pbs_code: string;
  created_at?: string;
}

interface InstancePBSMapping {
  id?: number;
  instance_id: string;
  pbs_level_id: number;
  pbs_instance_id: string; // 每个实例对应层级的唯一ID
  created_at?: string;
}

function createPBSTables() {
  const db = new Database(DB_PATH);

  try {
    // 创建PBS层级结构表
    const createPBSTable = `
      CREATE TABLE IF NOT EXISTS pbs_levels (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        level1_area TEXT NOT NULL,
        level2_analysis TEXT,
        level3_subitem TEXT NOT NULL,
        full_path TEXT NOT NULL,
        pbs_code TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    // 创建实例PBS映射表
    const createMappingTable = `
      CREATE TABLE IF NOT EXISTS instance_pbs_mapping (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instance_id TEXT NOT NULL,
        pbs_level_id INTEGER NOT NULL,
        pbs_instance_id TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (pbs_level_id) REFERENCES pbs_levels(id),
        UNIQUE(instance_id, pbs_level_id)
      )
    `;

    // 创建索引
    const createIndexes = [
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_area ON pbs_levels(level1_area)',
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_analysis ON pbs_levels(level2_analysis)',
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_code ON pbs_levels(pbs_code)',
      'CREATE INDEX IF NOT EXISTS idx_instance_pbs_instance ON instance_pbs_mapping(instance_id)',
      'CREATE INDEX IF NOT EXISTS idx_instance_pbs_level ON instance_pbs_mapping(pbs_level_id)'
    ];

    console.log('创建PBS层级结构表...');
    db.exec(createPBSTable);
    
    console.log('创建实例PBS映射表...');
    db.exec(createMappingTable);
    
    console.log('创建索引...');
    createIndexes.forEach(indexSQL => {
      db.exec(indexSQL);
    });

    console.log('PBS表结构创建完成！');
  } catch (error) {
    console.error('创建PBS表结构失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

function generatePBSCode(level1: string, level2: string, level3: string): string {
  // 生成PBS编码：区域-分析-子分项
  const area = level1.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4);
  const analysis = level2 ? level2.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4) : '';
  const subitem = level3.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 6);
  
  return analysis ? `${area}-${analysis}-${subitem}` : `${area}-${subitem}`;
}

function importPBSData() {
  const csvPath = path.join(process.cwd(), 'excel', 'PBS层级结构-0709.csv');
  
  if (!fs.existsSync(csvPath)) {
    throw new Error(`CSV文件不存在: ${csvPath}`);
  }

  const csvContent = fs.readFileSync(csvPath, 'utf-8');
  const lines = csvContent.split('\n').filter(line => line.trim());
  
  const db = new Database(DB_PATH);
  
  try {
    const insertStmt = db.prepare(`
      INSERT OR REPLACE INTO pbs_levels 
      (level1_area, level2_analysis, level3_subitem, full_path, pbs_code)
      VALUES (?, ?, ?, ?, ?)
    `);

    let currentArea = '';
    let currentAnalysis = '';
    let imported = 0;
    let skipped = 0;

    console.log('开始导入PBS数据...');

    for (const line of lines) {
      // 跳过表头
      if (line.includes('区域,分析,子分项')) continue;

      // 解析CSV行
      const columns = line.split(',').map(col => col.trim().replace(/^"|"$/g, ''));
      
      if (columns.length < 3) {
        skipped++;
        continue;
      }

      const [area, analysis, subitem] = columns;

      // 更新当前层级
      if (area && area !== '') {
        currentArea = area;
      }
      if (analysis && analysis !== '') {
        currentAnalysis = analysis;
      }

      // 必须有子分项
      if (!subitem || subitem === '') {
        skipped++;
        continue;
      }

      const fullPath = currentAnalysis ? 
        `${currentArea} > ${currentAnalysis} > ${subitem}` : 
        `${currentArea} > ${subitem}`;
      
      const pbsCode = generatePBSCode(currentArea, currentAnalysis, subitem);

      try {
        insertStmt.run(currentArea, currentAnalysis || null, subitem, fullPath, pbsCode);
        imported++;
      } catch (error) {
        console.warn(`跳过重复项: ${pbsCode}`);
        skipped++;
      }
    }

    console.log(`PBS数据导入完成！导入: ${imported} 条，跳过: ${skipped} 条`);
    
    // 显示导入结果统计
    const countStmt = db.prepare('SELECT COUNT(*) as total FROM pbs_levels');
    const total = countStmt.get() as { total: number };
    console.log(`数据库中共有 ${total.total} 条PBS层级记录`);

  } catch (error) {
    console.error('导入PBS数据失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

// 生成实例PBS层级ID的函数
export function generateInstancePBSId(instanceId: string, pbsLevelId: number): string {
  const timestamp = Date.now();
  const random = Math.random().toString(36).substring(2, 8);
  return `${instanceId}-PBS-${pbsLevelId}-${timestamp}-${random}`;
}

// 为实例分配PBS层级的函数
export function assignPBSToInstance(instanceId: string, pbsLevelId: number): string {
  const db = new Database(DB_PATH);
  
  try {
    // 检查是否已经分配过
    const existingStmt = db.prepare(`
      SELECT pbs_instance_id FROM instance_pbs_mapping 
      WHERE instance_id = ? AND pbs_level_id = ?
    `);
    const existing = existingStmt.get(instanceId, pbsLevelId) as { pbs_instance_id: string } | undefined;
    
    if (existing) {
      return existing.pbs_instance_id;
    }

    // 生成新的PBS实例ID
    const pbsInstanceId = generateInstancePBSId(instanceId, pbsLevelId);
    
    const insertStmt = db.prepare(`
      INSERT INTO instance_pbs_mapping (instance_id, pbs_level_id, pbs_instance_id)
      VALUES (?, ?, ?)
    `);
    
    insertStmt.run(instanceId, pbsLevelId, pbsInstanceId);
    
    return pbsInstanceId;
  } finally {
    db.close();
  }
}

// 获取所有PBS层级的函数
export function getAllPBSLevels(): PBSLevel[] {
  const db = new Database(DB_PATH);
  
  try {
    const stmt = db.prepare(`
      SELECT * FROM pbs_levels 
      ORDER BY level1_area, level2_analysis, level3_subitem
    `);
    return stmt.all() as PBSLevel[];
  } finally {
    db.close();
  }
}

// 获取实例的PBS映射
export function getInstancePBSMapping(instanceId: string): (InstancePBSMapping & PBSLevel)[] {
  const db = new Database(DB_PATH);
  
  try {
    const stmt = db.prepare(`
      SELECT 
        ipm.*,
        pl.level1_area,
        pl.level2_analysis,
        pl.level3_subitem,
        pl.full_path,
        pl.pbs_code
      FROM instance_pbs_mapping ipm
      JOIN pbs_levels pl ON ipm.pbs_level_id = pl.id
      WHERE ipm.instance_id = ?
      ORDER BY ipm.created_at
    `);
    return stmt.all(instanceId) as (InstancePBSMapping & PBSLevel)[];
  } finally {
    db.close();
  }
}

// 主执行函数
async function main() {
  try {
    console.log('开始创建PBS层级结构系统...');
    
    // 1. 创建表结构
    createPBSTables();
    
    // 2. 导入数据
    importPBSData();
    
    console.log('\nPBS层级结构系统创建完成！');
    console.log('\n使用方法：');
    console.log('1. 在实例中选择PBS层级时，调用 getAllPBSLevels() 获取所有层级');
    console.log('2. 选择层级后，调用 assignPBSToInstance(instanceId, pbsLevelId) 生成唯一ID');
    console.log('3. 使用 getInstancePBSMapping(instanceId) 查看实例的PBS映射');
    
  } catch (error) {
    console.error('执行失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (import.meta.url === `file://${process.argv[1]}`) {
  main();
}

export type { PBSLevel, InstancePBSMapping };